namespace SubSonic
{
    using SubSonic.Utilities;
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;

    public class SqlDataProvider : DataProvider
    {
        private static readonly DataSet dsColumns = new DataSet();
        private static readonly DataSet dsExtendedProperties = new DataSet();
        private static readonly DataSet dsFK = new DataSet();
        private static readonly DataSet dsIndex = new DataSet();
        private static readonly DataSet dsManyToManyCheck = new DataSet();
        private static readonly DataSet dsManyToManyMap = new DataSet();
        private static readonly DataSet dsPK = new DataSet();
        private DataTable dtParamSql;
        private const string EXTENDED_PROPERTIES_ALL = "SELECT \r\n    t.name AS [TABLE_NAME], \r\n    c.name AS [COLUMN_NAME], \r\n    ep.name AS [EXTENDED_NAME],\r\n    value AS [EXTENDED_VALUE]\r\nFROM sys.extended_properties AS ep\r\nINNER JOIN sys.tables AS t ON ep.major_id = t.object_id \r\nLEFT JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id\r\nWHERE class = 1 OR class = 3";
        private const string EXTENDED_PROPERTIES_COLUMNS_2000 = "SELECT \r\n    objname AS COLUMN_NAME,\r\n    [name] AS EXTENDED_NAME,\r\n    [value] AS EXTENDED_VALUE\r\nFROM ::fn_listextendedproperty (NULL, 'user', '{0}', 'TABLE', '{1}', 'COLUMN', DEFAULT);";
        private const string EXTENDED_PROPERTIES_TABLES_2000 = "SELECT \r\n    objname AS TABLE_NAME,\r\n    [name] AS EXTENDED_NAME,\r\n    [value] AS EXTENDED_VALUE\r\nFROM ::fn_listextendedproperty (NULL, 'user', '{0}', 'TABLE', default, NULL, NULL);";
        private const string FOREIGN_TABLE_LIST = "SELECT KCU.COLUMN_NAME,TC.TABLE_NAME\r\n                    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\n                    JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_NAME=RC.CONSTRAINT_NAME\r\n                    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  ON RC.UNIQUE_CONSTRAINT_NAME=TC.CONSTRAINT_NAME\r\n                    WHERE KCU.TABLE_NAME=@tblName";
        private const string FOREIGN_TABLE_LIST_ALL = "SELECT\r\n    FK_Table  = FK.TABLE_NAME,\r\n    FK_Column = CU.COLUMN_NAME,\r\n    PK_Table  = PK.TABLE_NAME,\r\n    PK_Column = PT.COLUMN_NAME, \r\n    Constraint_Name = C.CONSTRAINT_NAME,\r\n    Owner = FK.TABLE_SCHEMA\r\nFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C\r\nINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME\r\nINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME\r\nINNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME\r\nINNER JOIN\r\n    (\t\r\n        SELECT i1.TABLE_NAME, i2.COLUMN_NAME\r\n        FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1\r\n        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME\r\n        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'\r\n    ) \r\nPT ON PT.TABLE_NAME = PK.TABLE_NAME";
        private const string GET_FOREIGN_KEY_SQL = "SELECT TC.TABLE_NAME AS TableName\r\n                                           FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\n                                           JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_NAME=RC.CONSTRAINT_NAME\r\n                                           JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  ON RC.UNIQUE_CONSTRAINT_NAME=TC.CONSTRAINT_NAME\r\n                                           WHERE KCU.COLUMN_NAME=@columnName AND KCU.TABLE_NAME = @tblName";
        private const string GET_PRIMARY_KEY_SQL = "SELECT KCU.TABLE_NAME AS TableName, KCU.COLUMN_NAME AS ColumnName\r\n                                           FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\n                                           JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_NAME=RC.CONSTRAINT_NAME\r\n                                           JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON RC.UNIQUE_CONSTRAINT_NAME=TC.CONSTRAINT_NAME\r\n                                           WHERE TC.TABLE_NAME = @tblName";
        private const string GET_TABLE_SQL = "SELECT KCU.TABLE_NAME as TableName\r\n                                            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\n                                            JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC\r\n                                            ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME\r\n                                            WHERE KCU.COLUMN_NAME=@columnName AND TC.CONSTRAINT_TYPE='PRIMARY KEY'";
        private const string INDEX_SQL_ALL = "SELECT\r\n    KCU.TABLE_NAME as TableName,\r\n    KCU.TABLE_SCHEMA as Owner,\r\n    KCU.COLUMN_NAME as ColumnName, \r\n    TC.CONSTRAINT_TYPE as ConstraintType \r\nFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\nJOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC\r\nON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME";
        private const string MANY_TO_MANY_CHECK_ALL = "SELECT \r\n    FK_Table = FK.TABLE_NAME, \r\n    FK_Column = CU.COLUMN_NAME, \r\n    PK_Table  = PK.TABLE_NAME, \r\n    PK_Column = PT.COLUMN_NAME, \r\n    Constraint_Name = C.CONSTRAINT_NAME\r\nFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C\r\nINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME\r\nINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME\r\nINNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME\r\nINNER JOIN    \r\n(    \r\n    SELECT i1.TABLE_NAME, i2.COLUMN_NAME\r\n    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1\r\n    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME\r\n    WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'\r\n)\r\nPT ON PT.TABLE_NAME = PK.TABLE_NAME\r\n\r\nWHERE FK.TABLE_NAME IN\r\n    (\r\n        SELECT tc.table_Name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc\r\n        JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON tc.Constraint_name = kcu.Constraint_Name\r\n        JOIN \r\n        (\r\n            SELECT tc.Table_Name, kcu.Column_Name AS Column_Name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc\r\n            JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON tc.Constraint_name = kcu.Constraint_Name\r\n            WHERE Constraint_Type = 'FOREIGN KEY'\r\n        ) \r\n        AS t ON t.Table_Name = tc.table_Name AND t.Column_Name = kcu.Column_Name\r\n        WHERE Constraint_Type = 'PRIMARY KEY'\r\n        GROUP BY tc.Constraint_Name, tc.Table_Name HAVING COUNT(*) > 1   \r\n    )";
        private const string MANY_TO_MANY_FOREIGN_MAP_ALL = "SELECT \r\n    FK_Table  = FK.TABLE_NAME,\r\n    FK_Column = CU.COLUMN_NAME,\r\n    PK_Table  = PK.TABLE_NAME,\r\n    PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME\r\nFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C\r\nINNER JOIN \tINFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME\r\nINNER JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME\r\nINNER JOIN  INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME\r\nINNER JOIN\t\r\n    (\r\n        SELECT i1.TABLE_NAME, i2.COLUMN_NAME\r\n        FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1\r\n        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME\r\n        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'\r\n    ) \r\nPT ON PT.TABLE_NAME = PK.TABLE_NAME";
        private const string PAGING_SQL = "\t\t\t\t\t\r\n\t\t\t\t\tDECLARE @Page int\r\n\t\t\t\t\tDECLARE @PageSize int\r\n\r\n\t\t\t\t\tSET @Page = {5}\r\n\t\t\t\t\tSET @PageSize = {6}\r\n\r\n\t\t\t\t\tSET NOCOUNT ON\r\n\r\n\t\t\t\t\t-- create a temp table to hold order ids\r\n\t\t\t\t\tDECLARE @TempTable TABLE (IndexId int identity, _keyID {7})\r\n\r\n\t\t\t\t\t-- insert the table ids and row numbers into the memory table\r\n\t\t\t\t\tINSERT INTO @TempTable\r\n\t\t\t\t\t(\r\n\t\t\t\t\t  _keyID\r\n\t\t\t\t\t)\r\n\t\t\t\t\tSELECT \r\n\t\t\t\t\t\t{0}\r\n\t\t\t\t\tFROM\r\n\t\t\t\t\t\t{1}\r\n\t\t\t\t\t{3}\r\n\t\t\t\t\t{4}\r\n\r\n\t\t\t\t\t-- select only those rows belonging to the proper page\r\n\t\t\t\t\tSELECT \r\n\t\t\t\t\t{2}\r\n\t\t\t\t\tFROM {1}\r\n\t\t\t\t\tINNER JOIN @TempTable t ON {1}.{0} = t._keyID\r\n\t\t\t\t\tWHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)\r\n                    {4}";
        private const string PAGING_VIEW_SQL = "\t\t\t\t\t\r\n\t\t\t\t\tDECLARE @Page int\r\n\t\t\t\t\tDECLARE @PageSize int\r\n\r\n\t\t\t\t\tSET @Page = {3}\r\n\t\t\t\t\tSET @PageSize = {4}\r\n\r\n\t\t\t\t\tSET NOCOUNT ON\r\n                \r\n                    \r\n                    SELECT * INTO #temp FROM {0} WHERE 1 = 0\r\n                    ALTER TABLE #temp ADD _indexID int PRIMARY KEY IDENTITY(1,1)\r\n                    \r\n                    INSERT INTO #temp SELECT * FROM {0} {1} {2}\r\n                    \r\n\r\n                    SELECT * FROM #temp\r\n                    WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)\r\n\t\t\t\t\t\r\n                    --clean up\r\n                    DROP TABLE #temp\r\n                    ";
        private const string PRIMARY_TABLE_LIST_ALL = "SELECT \r\n    TC.TABLE_NAME AS PK_TABLE,\r\n    KCU.TABLE_NAME AS FK_TABLE,\r\n    KCU.COLUMN_NAME AS FK_COLUMN\r\nFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\nJOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_NAME=RC.CONSTRAINT_NAME\r\nJOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON RC.UNIQUE_CONSTRAINT_NAME=TC.CONSTRAINT_NAME";
        private const string SP_PARAM_SQL_ALL = "SELECT SPECIFIC_NAME AS SPName, ORDINAL_POSITION AS OrdinalPosition, \r\n                                                PARAMETER_MODE AS ParamType, IS_RESULT AS IsResult, PARAMETER_NAME AS Name, \r\n                                                DATA_TYPE AS DataType, CHARACTER_MAXIMUM_LENGTH AS DataLength, REPLACE(PARAMETER_NAME, '@', '') \r\n                                                AS CleanName, PARAMETER_MODE as [mode]\r\n                                                FROM INFORMATION_SCHEMA.PARAMETERS";
        private const string SP_SQL = "SELECT SPECIFIC_NAME AS SPName, ROUTINE_DEFINITION AS SQL, CREATED AS CreatedOn, LAST_ALTERED AS ModifiedOn \r\n                                    FROM INFORMATION_SCHEMA.ROUTINES\r\n                                    WHERE ROUTINE_TYPE='PROCEDURE' AND SPECIFIC_NAME NOT LIKE 'sp_%diagram%'";
        private const string TABLE_BY_PK = "SELECT TC.TABLE_NAME as tableName \r\n                        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\n                        JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC\r\n                        ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME \r\n                        WHERE column_name=@columnName\r\n                        AND CONSTRAINT_TYPE='PRIMARY KEY'\r\n                        AND TC.TABLE_NAME NOT LIKE '%'+@mapSuffix\r\n                        AND KCU.ORDINAL_POSITION=1";
        private const string TABLE_COLUMN_SQL_ALL = "SELECT \r\n    TABLE_CATALOG AS [Database],\r\n    TABLE_SCHEMA AS Owner, \r\n    TABLE_NAME AS TableName, \r\n    COLUMN_NAME AS ColumnName, \r\n    ORDINAL_POSITION AS OrdinalPosition, \r\n    COLUMN_DEFAULT AS DefaultSetting, \r\n    IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType, \r\n    CHARACTER_MAXIMUM_LENGTH AS MaxLength, \r\n    DATETIME_PRECISION AS DatePrecision,\r\n    COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IsIdentity,\r\n    COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsComputed') as IsComputed\r\nFROM  INFORMATION_SCHEMA.COLUMNS\r\nORDER BY OrdinalPosition ASC";
        private const string TABLE_SQL = "SELECT TABLE_CATALOG AS [Database], TABLE_SCHEMA AS Owner, TABLE_NAME AS Name, TABLE_TYPE \r\n                                        FROM INFORMATION_SCHEMA.TABLES\r\n                                        WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <> N'sysdiagrams') AND (TABLE_NAME <> N'dtproperties')";
        private const string VIEW_SQL = "SELECT TABLE_CATALOG AS [Database], TABLE_SCHEMA AS Owner, TABLE_NAME AS Name, TABLE_TYPE\r\n                                        FROM INFORMATION_SCHEMA.TABLES\r\n                                        WHERE (TABLE_TYPE = 'VIEW') AND (TABLE_NAME <> N'sysdiagrams')";

        private static void AddParams(SqlCommand cmd, QueryCommand qry)
        {
            if (qry.Parameters != null)
            {
                foreach (QueryParameter parameter in qry.Parameters)
                {
                    SqlParameter parameter2 = new SqlParameter(parameter.ParameterName, Utility.GetSqlDBType(parameter.DataType));
                    parameter2.Direction = parameter.Mode;
                    if ((parameter2.Direction == ParameterDirection.Output) || (parameter2.Direction == ParameterDirection.InputOutput))
                    {
                        parameter2.Size = parameter.Size;
                    }
                    if ((parameter.ParameterValue == null) || Utility.IsMatch(parameter.ParameterValue.ToString(), "null"))
                    {
                        parameter2.Value = DBNull.Value;
                    }
                    else if (parameter.DataType == DbType.Guid)
                    {
                        string str = parameter.ParameterValue.ToString();
                        if (!string.IsNullOrEmpty(str))
                        {
                            if (!Utility.IsMatch(str, "DEFAULT"))
                            {
                                parameter2.Value = new Guid(parameter.ParameterValue.ToString());
                            }
                        }
                        else
                        {
                            parameter2.Value = DBNull.Value;
                        }
                    }
                    else
                    {
                        parameter2.Value = parameter.ParameterValue;
                    }
                    cmd.Parameters.Add(parameter2);
                }
            }
        }

        public override QueryCommand BuildCommand(Query qry)
        {
            switch (qry.QueryType)
            {
                case QueryType.Select:
                    return base.BuildSelectCommand(qry);

                case QueryType.Update:
                    return base.BuildUpdateCommand(qry);

                case QueryType.Insert:
                    return null;

                case QueryType.Delete:
                    return base.BuildDeleteCommand(qry);
            }
            return null;
        }

        private static void CheckoutOutputParams(SqlCommand cmd, QueryCommand qry)
        {
            if ((qry.CommandType == CommandType.StoredProcedure) && qry.HasOutputParams())
            {
                foreach (QueryParameter parameter in qry.Parameters)
                {
                    if (((parameter.Mode == ParameterDirection.InputOutput) || (parameter.Mode == ParameterDirection.Output)) || (parameter.Mode == ParameterDirection.ReturnValue))
                    {
                        object item = cmd.Parameters[parameter.ParameterName].Value;
                        parameter.ParameterValue = item;
                        qry.OutputValues.Add(item);
                    }
                }
            }
        }

        internal override DbConnection CreateConnection()
        {
            return this.CreateConnection(base.DefaultConnectionString);
        }

        internal override DbConnection CreateConnection(string newConnectionString)
        {
            SqlConnection connection = new SqlConnection(newConnectionString);
            connection.Open();
            return connection;
        }

        public override string DelimitDbName(string columnName)
        {
            if ((!string.IsNullOrEmpty(columnName) && !columnName.StartsWith("[")) && !columnName.EndsWith("]"))
            {
                return ("[" + columnName + "]");
            }
            return string.Empty;
        }

        public override int ExecuteQuery(QueryCommand qry)
        {
            using (AutomaticConnectionScope scope = new AutomaticConnectionScope(this))
            {
                SqlCommand cmd = new SqlCommand(qry.CommandSql);
                cmd.CommandType = qry.CommandType;
                cmd.CommandTimeout = qry.CommandTimeout;
                AddParams(cmd, qry);
                cmd.Connection = (SqlConnection) scope.Connection;
                int num = cmd.ExecuteNonQuery();
                CheckoutOutputParams(cmd, qry);
                return num;
            }
        }

        public override object ExecuteScalar(QueryCommand qry)
        {
            using (AutomaticConnectionScope scope = new AutomaticConnectionScope(this))
            {
                SqlCommand cmd = new SqlCommand(qry.CommandSql);
                cmd.CommandType = qry.CommandType;
                cmd.CommandTimeout = qry.CommandTimeout;
                AddParams(cmd, qry);
                cmd.Connection = (SqlConnection) scope.Connection;
                object obj2 = cmd.ExecuteScalar();
                CheckoutOutputParams(cmd, qry);
                return obj2;
            }
        }

        public override void ExecuteTransaction(QueryCommandCollection commands)
        {
            if (commands.Count > 0)
            {
                SqlCommand cmd = null;
                using (AutomaticConnectionScope scope = new AutomaticConnectionScope(this))
                {
                    if (scope.Connection.State == ConnectionState.Closed)
                    {
                        scope.Connection.Open();
                    }
                    SqlTransaction transaction = (SqlTransaction) scope.Connection.BeginTransaction();
                    foreach (QueryCommand command2 in commands)
                    {
                        if (command2.CommandType == CommandType.Text)
                        {
                            command2.CommandSql = "/* ExecuteTransaction() */ " + command2.CommandSql;
                        }
                        cmd = new SqlCommand(command2.CommandSql, (SqlConnection) scope.Connection, transaction);
                        cmd.CommandType = command2.CommandType;
                        AddParams(cmd, command2);
                        try
                        {
                            cmd.ExecuteNonQuery();
                            continue;
                        }
                        catch (SqlException exception)
                        {
                            transaction.Rollback();
                            scope.Connection.Close();
                            cmd.Dispose();
                            throw new Exception(exception.Message);
                        }
                    }
                    transaction.Commit();
                    scope.Connection.Close();
                    if (cmd != null)
                    {
                        cmd.Dispose();
                    }
                    return;
                }
            }
            throw new Exception("No commands present");
        }

        public override IDbCommand GetCommand(QueryCommand qry)
        {
            SqlCommand cmd = new SqlCommand(qry.CommandSql);
            AddParams(cmd, qry);
            return cmd;
        }

        internal override string GetDatabaseVersion(string providerName)
        {
            QueryCommand cmd = new QueryCommand("/* GetDatabaseVersion */ SELECT @@version", providerName);
            object obj2 = DataService.ExecuteScalar(cmd);
            if (obj2 != null)
            {
                return obj2.ToString();
            }
            return "Unknown";
        }

        public override T GetDataSet<T>(QueryCommand qry) //where T: DataSet, new()
        {
            T ds = Activator.CreateInstance<T>();
            if (qry.CommandType == CommandType.Text)
            {
                qry.CommandSql = "/* GetDataSet() */ " + qry.CommandSql;
            }
            SqlCommand selectCommand = new SqlCommand(qry.CommandSql);
            selectCommand.CommandType = qry.CommandType;
            selectCommand.CommandTimeout = qry.CommandTimeout;
            SqlDataAdapter da = new SqlDataAdapter(selectCommand);
            DataProvider.AddTableMappings(da, ds);
            using (AutomaticConnectionScope scope = new AutomaticConnectionScope(this))
            {
                selectCommand.Connection = (SqlConnection) scope.Connection;
                AddParams(selectCommand, qry);
                da.Fill(ds);
                CheckoutOutputParams(selectCommand, qry);
                return ds;
            }
        }

        public override DbCommand GetDbCommand(QueryCommand qry)
        {
            AutomaticConnectionScope scope = new AutomaticConnectionScope(this);
            DbCommand command = scope.Connection.CreateCommand();
            command.CommandText = qry.CommandSql;
            command.CommandType = qry.CommandType;
            foreach (QueryParameter parameter in qry.Parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        public override DbType GetDbType(string sqlType)
        {
            switch (sqlType)
            {
                case "varchar":
                    return DbType.String;

                case "nvarchar":
                    return DbType.String;

                case "int":
                    return DbType.Int32;

                case "uniqueidentifier":
                    return DbType.Guid;

                case "datetime":
                    return DbType.DateTime;

                case "bigint":
                    return DbType.Int64;

                case "binary":
                    return DbType.Binary;

                case "bit":
                    return DbType.Boolean;

                case "char":
                    return DbType.AnsiStringFixedLength;

                case "decimal":
                    return DbType.Decimal;

                case "float":
                    return DbType.Double;

                case "image":
                    return DbType.Binary;

                case "money":
                    return DbType.Currency;

                case "nchar":
                    return DbType.String;

                case "ntext":
                    return DbType.String;

                case "numeric":
                    return DbType.Decimal;

                case "real":
                    return DbType.Decimal;

                case "smalldatetime":
                    return DbType.DateTime;

                case "smallint":
                    return DbType.Int16;

                case "smallmoney":
                    return DbType.Currency;

                case "sql_variant":
                    return DbType.String;

                case "sysname":
                    return DbType.String;

                case "text":
                    return DbType.String;

                case "timestamp":
                    return DbType.Binary;

                case "tinyint":
                    return DbType.Byte;

                case "varbinary":
                    return DbType.Binary;
            }
            return DbType.String;
        }

        public override string GetForeignKeyTableName(string fkColumnName)
        {
            QueryCommand cmd = new QueryCommand("/* GetForeignKeyTableName(" + fkColumnName + ") */ SELECT KCU.TABLE_NAME as TableName\r\n                                            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\n                                            JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC\r\n                                            ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME\r\n                                            WHERE KCU.COLUMN_NAME=@columnName AND TC.CONSTRAINT_TYPE='PRIMARY KEY'", this.Name);
            cmd.AddParameter("columnName", fkColumnName);
            object obj2 = this.ExecuteScalar(cmd);
            if (obj2 == null)
            {
                return null;
            }
            return obj2.ToString();
        }

        public override string GetForeignKeyTableName(string fkColumnName, string tableName)
        {
            QueryCommand cmd = new QueryCommand("/* GetForeignKeyTableName(" + fkColumnName + "," + tableName + ") */ SELECT TC.TABLE_NAME AS TableName\r\n                                           FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\n                                           JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_NAME=RC.CONSTRAINT_NAME\r\n                                           JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  ON RC.UNIQUE_CONSTRAINT_NAME=TC.CONSTRAINT_NAME\r\n                                           WHERE KCU.COLUMN_NAME=@columnName AND KCU.TABLE_NAME = @tblName", this.Name);
            cmd.AddParameter("columnName", fkColumnName);
            cmd.AddParameter("tblName", tableName);
            object obj2 = this.ExecuteScalar(cmd);
            if (obj2 == null)
            {
                return null;
            }
            return obj2.ToString();
        }

        public override string[] GetForeignKeyTables(string tableName)
        {
            QueryCommand cmd = new QueryCommand("/* GetForeignKeyTables(" + tableName + ") */ SELECT KCU.COLUMN_NAME,TC.TABLE_NAME\r\n                    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\n                    JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_NAME=RC.CONSTRAINT_NAME\r\n                    JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  ON RC.UNIQUE_CONSTRAINT_NAME=TC.CONSTRAINT_NAME\r\n                    WHERE KCU.TABLE_NAME=@tblName", this.Name);
            cmd.AddParameter("@tblName", tableName);
            StringBuilder builder = new StringBuilder();
            using (IDataReader reader = this.GetReader(cmd))
            {
                while (reader.Read())
                {
                    builder.Append(reader["TABLE_NAME"]);
                    builder.Append("|");
                }
                reader.Close();
            }
            return builder.ToString().Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
        }

        public override string GetInsertSql(Query qry)
        {
            TableSchema.Table schema = qry.Schema;
            string str = "/* GetInsertSql(" + schema.Name + ") */ INSERT INTO " + Utility.QualifyColumnName(schema.SchemaName, schema.Name, qry.Provider);
            string str2 = string.Empty;
            string str3 = string.Empty;
            bool flag = false;
            string str4 = "";
            bool flag2 = true;
            foreach (TableSchema.TableColumn column in schema.Columns)
            {
                string columnName = column.ColumnName;
                if (((column.DataType != DbType.Guid) || (column.DefaultSetting == null)) || (column.DefaultSetting != "DEFAULT"))
                {
                    if (!column.AutoIncrement && !column.IsReadOnly)
                    {
                        if (!flag2)
                        {
                            str2 = str2 + ",";
                            str3 = str3 + ",";
                        }
                        flag2 = false;
                        str2 = str2 + this.DelimitDbName(columnName);
                        str3 = str3 + Utility.PrefixParameter(columnName, this);
                    }
                    if (column.IsPrimaryKey && (column.DataType == DbType.Guid))
                    {
                        str4 = column.ColumnName;
                        flag = true;
                    }
                }
            }
            str = str + "(" + str2 + ") ";
            string str6 = "SELECT SCOPE_IDENTITY() AS newID;";
            if (flag)
            {
                if (Utility.IsSql2005(this))
                {
                    str = str + " OUTPUT INSERTED.[" + str4 + "]";
                }
                else
                {
                    str6 = " SELECT @" + str4 + " as newID;";
                }
            }
            return ((str + "VALUES(" + str3 + ");") + str6);
        }

        public override string GetParameterPrefix()
        {
            return "@";
        }

        public override ArrayList GetPrimaryKeyTableNames(string tableName)
        {
            QueryCommand cmd = new QueryCommand("/* GetPrimaryKeyTableNames(" + tableName + ") */ SELECT KCU.TABLE_NAME AS TableName, KCU.COLUMN_NAME AS ColumnName\r\n                                           FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\n                                           JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_NAME=RC.CONSTRAINT_NAME\r\n                                           JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON RC.UNIQUE_CONSTRAINT_NAME=TC.CONSTRAINT_NAME\r\n                                           WHERE TC.TABLE_NAME = @tblName", this.Name);
            cmd.AddParameter("tblName", tableName);
            ArrayList list = new ArrayList();
            using (IDataReader reader = this.GetReader(cmd))
            {
                while (reader.Read())
                {
                    list.Add(new string[] { reader["TableName"].ToString(), reader["ColumnName"].ToString() });
                }
                reader.Close();
            }
            return list;
        }

        public override TableSchema.Table[] GetPrimaryKeyTables(string tableName)
        {
            QueryCommand cmd = new QueryCommand("/* GetPrimaryKeyTables(" + tableName + ") */ SELECT KCU.TABLE_NAME AS TableName, KCU.COLUMN_NAME AS ColumnName\r\n                                           FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\n                                           JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_NAME=RC.CONSTRAINT_NAME\r\n                                           JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON RC.UNIQUE_CONSTRAINT_NAME=TC.CONSTRAINT_NAME\r\n                                           WHERE TC.TABLE_NAME = @tblName", this.Name);
            cmd.AddParameter("tblName", tableName);
            ArrayList list = new ArrayList();
            using (IDataReader reader = this.GetReader(cmd))
            {
                while (reader.Read())
                {
                    list.Add(reader["TableName"].ToString());
                }
                reader.Close();
            }
            if (list.Count <= 0)
            {
                return null;
            }
            TableSchema.Table[] tableArray = new TableSchema.Table[list.Count];
            for (int i = 0; i < list.Count; i++)
            {
                tableArray[i] = DataService.GetSchema((string) list[i], this.Name, TableType.Table);
            }
            return tableArray;
        }

        protected static string GetQualifiedSelect(TableSchema.Table table)
        {
            StringBuilder builder = new StringBuilder();
            foreach (TableSchema.TableColumn column in table.Columns)
            {
                builder.AppendFormat(", [{0}].[{1}].[{2}]", table.SchemaName, table.Name, column.ColumnName);
            }
            string str = builder.ToString();
            if (str.Length > 1)
            {
                str = builder.ToString().Substring(1);
            }
            return str;
        }

        public override IDataReader GetReader(QueryCommand qry)
        {
            IDataReader reader;
            AutomaticConnectionScope scope = new AutomaticConnectionScope(this);
            SqlCommand cmd = new SqlCommand(qry.CommandSql);
            cmd.CommandType = qry.CommandType;
            cmd.CommandTimeout = qry.CommandTimeout;
            AddParams(cmd, qry);
            cmd.Connection = (SqlConnection) scope.Connection;
            try
            {
                if (scope.IsUsingSharedConnection)
                {
                    reader = cmd.ExecuteReader();
                }
                else
                {
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            catch (SqlException)
            {
                scope.Dispose();
                throw;
            }
            CheckoutOutputParams(cmd, qry);
            return reader;
        }

        public override string GetSelectSql(Query qry)
        {
            string selectList;
            TableSchema.Table schema = qry.Schema;
            string str = qry.IsDistinct ? "DISTINCT " : string.Empty;
            string str2 = "/* GetSelectSql(" + schema.Name + ") */ SELECT " + str + "TOP " + qry.Top + " ";
            StringBuilder builder = new StringBuilder();
            StringBuilder builder2 = new StringBuilder();
            if ((qry.SelectList != null) && (qry.SelectList.Trim().Length >= 2))
            {
                selectList = qry.SelectList;
            }
            else
            {
                selectList = GetQualifiedSelect(schema);
            }
            string str4 = DataProvider.BuildWhere(qry);
            if (qry.OrderByCollection.Count > 0)
            {
                for (int i = 0; i < qry.OrderByCollection.Count; i++)
                {
                    string orderString = qry.OrderByCollection[i].OrderString;
                    if (!string.IsNullOrEmpty(orderString))
                    {
                        builder.Append(orderString);
                        if ((i + 1) != qry.OrderByCollection.Count)
                        {
                            builder.Append(", ");
                        }
                    }
                }
            }
            else if (schema.PrimaryKey != null)
            {
                builder.Append(OrderBy.Asc(schema.PrimaryKey.ColumnName).OrderString);
            }
            if (qry.PageIndex < 0)
            {
                builder2.Append(str2);
                builder2.Append(selectList);
                builder2.Append(" FROM ");
                builder2.Append(Utility.QualifyColumnName(schema.SchemaName, schema.Name, qry.Provider));
                builder2.Append(str4);
                builder2.Append(builder);
                builder2.Append(";");
            }
            else if (schema.PrimaryKey != null)
            {
                builder2.Append(string.Format("\t\t\t\t\t\r\n\t\t\t\t\tDECLARE @Page int\r\n\t\t\t\t\tDECLARE @PageSize int\r\n\r\n\t\t\t\t\tSET @Page = {5}\r\n\t\t\t\t\tSET @PageSize = {6}\r\n\r\n\t\t\t\t\tSET NOCOUNT ON\r\n\r\n\t\t\t\t\t-- create a temp table to hold order ids\r\n\t\t\t\t\tDECLARE @TempTable TABLE (IndexId int identity, _keyID {7})\r\n\r\n\t\t\t\t\t-- insert the table ids and row numbers into the memory table\r\n\t\t\t\t\tINSERT INTO @TempTable\r\n\t\t\t\t\t(\r\n\t\t\t\t\t  _keyID\r\n\t\t\t\t\t)\r\n\t\t\t\t\tSELECT \r\n\t\t\t\t\t\t{0}\r\n\t\t\t\t\tFROM\r\n\t\t\t\t\t\t{1}\r\n\t\t\t\t\t{3}\r\n\t\t\t\t\t{4}\r\n\r\n\t\t\t\t\t-- select only those rows belonging to the proper page\r\n\t\t\t\t\tSELECT \r\n\t\t\t\t\t{2}\r\n\t\t\t\t\tFROM {1}\r\n\t\t\t\t\tINNER JOIN @TempTable t ON {1}.{0} = t._keyID\r\n\t\t\t\t\tWHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)\r\n                    {4}", new object[] { schema.PrimaryKey.ColumnName, Utility.QualifyColumnName(schema.SchemaName, schema.Name, qry.Provider), selectList, str4, builder, qry.PageIndex, qry.PageSize, Utility.GetSqlDBType(schema.PrimaryKey.DataType) }));
                builder2.Append(";");
            }
            else
            {
                builder2.Append(string.Format("\t\t\t\t\t\r\n\t\t\t\t\tDECLARE @Page int\r\n\t\t\t\t\tDECLARE @PageSize int\r\n\r\n\t\t\t\t\tSET @Page = {3}\r\n\t\t\t\t\tSET @PageSize = {4}\r\n\r\n\t\t\t\t\tSET NOCOUNT ON\r\n                \r\n                    \r\n                    SELECT * INTO #temp FROM {0} WHERE 1 = 0\r\n                    ALTER TABLE #temp ADD _indexID int PRIMARY KEY IDENTITY(1,1)\r\n                    \r\n                    INSERT INTO #temp SELECT * FROM {0} {1} {2}\r\n                    \r\n\r\n                    SELECT * FROM #temp\r\n                    WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)\r\n\t\t\t\t\t\r\n                    --clean up\r\n                    DROP TABLE #temp\r\n                    ", new object[] { Utility.QualifyColumnName(schema.SchemaName, schema.Name, qry.Provider), str4, builder, qry.PageIndex, qry.PageSize }));
                builder2.Append(";");
            }
            return builder2.ToString();
        }

        public override string[] GetSPList()
        {
            QueryCommand cmd = new QueryCommand("/* GetSPList() */ SELECT SPECIFIC_NAME AS SPName, ROUTINE_DEFINITION AS SQL, CREATED AS CreatedOn, LAST_ALTERED AS ModifiedOn \r\n                                    FROM INFORMATION_SCHEMA.ROUTINES\r\n                                    WHERE ROUTINE_TYPE='PROCEDURE' AND SPECIFIC_NAME NOT LIKE 'sp_%diagram%'", this.Name);
            StringBuilder builder = new StringBuilder();
            using (IDataReader reader = this.GetReader(cmd))
            {
                while (reader.Read())
                {
                    builder.Append(reader[0]);
                    builder.Append("|");
                }
                reader.Close();
            }
            return builder.ToString().Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
        }

        public override IDataReader GetSPParams(string spName)
        {
            if (this.dtParamSql == null)
            {
                QueryCommand cmd = new QueryCommand("SELECT SPECIFIC_NAME AS SPName, ORDINAL_POSITION AS OrdinalPosition, \r\n                                                PARAMETER_MODE AS ParamType, IS_RESULT AS IsResult, PARAMETER_NAME AS Name, \r\n                                                DATA_TYPE AS DataType, CHARACTER_MAXIMUM_LENGTH AS DataLength, REPLACE(PARAMETER_NAME, '@', '') \r\n                                                AS CleanName, PARAMETER_MODE as [mode]\r\n                                                FROM INFORMATION_SCHEMA.PARAMETERS", this.Name);
                this.dtParamSql = new DataTable();
                this.dtParamSql.Load(this.GetReader(cmd));
            }
            DataView view = new DataView(this.dtParamSql);
            view.RowFilter = "SPName = '" + spName + "'";
            view.Sort = "OrdinalPosition";
            return view.ToTable().CreateDataReader();
        }

        public override string GetSql(Query qry)
        {
            string str = string.Empty;
            switch (qry.QueryType)
            {
                case QueryType.Select:
                    return this.GetSelectSql(qry);

                case QueryType.Update:
                    return base.GetUpdateSql(qry);

                case QueryType.Insert:
                    return this.GetInsertSql(qry);

                case QueryType.Delete:
                    return DataProvider.GetDeleteSql(qry);
            }
            return str;
        }

        public override string GetTableNameByPrimaryKey(string pkName, string providerName)
        {
            QueryCommand cmd = new QueryCommand("/* GetTableNameByPrimaryKey(" + pkName + ") */ SELECT TC.TABLE_NAME as tableName \r\n                        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\n                        JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC\r\n                        ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME \r\n                        WHERE column_name=@columnName\r\n                        AND CONSTRAINT_TYPE='PRIMARY KEY'\r\n                        AND TC.TABLE_NAME NOT LIKE '%'+@mapSuffix\r\n                        AND KCU.ORDINAL_POSITION=1", providerName);
            cmd.Parameters.Add("@columnName", pkName);
            cmd.Parameters.Add("@mapSuffix", base.ManyToManySuffix);
            object obj2 = DataService.ExecuteScalar(cmd);
            string str = string.Empty;
            if (obj2 != null)
            {
                str = obj2.ToString();
            }
            return str;
        }

        public override string[] GetTableNameList()
        {
            if ((base.TableNames == null) || !base.CurrentConnectionStringIsDefault)
            {
                QueryCommand cmd = new QueryCommand("/* GetTableNameList() */ SELECT TABLE_CATALOG AS [Database], TABLE_SCHEMA AS Owner, TABLE_NAME AS Name, TABLE_TYPE \r\n                                        FROM INFORMATION_SCHEMA.TABLES\r\n                                        WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <> N'sysdiagrams') AND (TABLE_NAME <> N'dtproperties')", this.Name);
                StringBuilder builder = new StringBuilder();
                using (IDataReader reader = this.GetReader(cmd))
                {
                    while (reader.Read())
                    {
                        builder.Append(reader["Name"]);
                        builder.Append("|");
                    }
                    reader.Close();
                }
                string[] array = builder.ToString().Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
                Array.Sort<string>(array);
                if (!base.CurrentConnectionStringIsDefault)
                {
                    return array;
                }
                base.TableNames = array;
            }
            return base.TableNames;
        }

        public override TableSchema.Table GetTableSchema(string tableName, TableType tableType)
        {
            TableSchema.TableColumnCollection columns = new TableSchema.TableColumnCollection();
            TableSchema.Table tblSchema = new TableSchema.Table(tableName, tableType, this);
            this.SetExtendedTableProperties(tblSchema);
            tblSchema.ForeignKeys = new TableSchema.ForeignKeyTableCollection();
            if (dsColumns.Tables[this.Name] == null)
            {
                QueryCommand cmd = new QueryCommand("SELECT \r\n    TABLE_CATALOG AS [Database],\r\n    TABLE_SCHEMA AS Owner, \r\n    TABLE_NAME AS TableName, \r\n    COLUMN_NAME AS ColumnName, \r\n    ORDINAL_POSITION AS OrdinalPosition, \r\n    COLUMN_DEFAULT AS DefaultSetting, \r\n    IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType, \r\n    CHARACTER_MAXIMUM_LENGTH AS MaxLength, \r\n    DATETIME_PRECISION AS DatePrecision,\r\n    COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IsIdentity,\r\n    COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsComputed') as IsComputed\r\nFROM  INFORMATION_SCHEMA.COLUMNS\r\nORDER BY OrdinalPosition ASC", this.Name);
                DataTable table = new DataTable(this.Name);
                table.Load(this.GetReader(cmd));
                dsColumns.Tables.Add(table);
            }
            DataRow[] rowArray = dsColumns.Tables[this.Name].Select("TableName ='" + tableName + "'", "OrdinalPosition ASC");
            for (int i = 0; i < rowArray.Length; i++)
            {
                int num2;
                string dataType = rowArray[i]["DataType"].ToString().ToLower();
                TableSchema.TableColumn item = new TableSchema.TableColumn(tblSchema);
                item.ColumnName = rowArray[i]["ColumnName"].ToString();
                item.DataType = this.GetDbType(dataType);
                if (rowArray[i]["DefaultSetting"] != DBNull.Value)
                {
                    string str2 = rowArray[i]["DefaultSetting"].ToString().Trim();
                    if (str2.ToLower().IndexOf("newsequentialid()") > -1)
                    {
                        item.DefaultSetting = "DEFAULT";
                    }
                    else
                    {
                        item.DefaultSetting = str2;
                    }
                }
                item.AutoIncrement = Convert.ToBoolean(rowArray[i]["IsIdentity"]);
                int.TryParse(rowArray[i]["MaxLength"].ToString(), out num2);
                item.MaxLength = num2;
                item.IsNullable = rowArray[i]["IsNullable"].ToString() == "YES";
                bool flag = rowArray[i]["IsComputed"].ToString() == "1";
                item.IsReadOnly = (dataType == "timestamp") || flag;
                columns.Add(item);
                tblSchema.SchemaName = rowArray[i]["Owner"].ToString();
                this.SetExtendedColumnProperties(tblSchema, item);
            }
            if (dsIndex.Tables[this.Name] == null)
            {
                QueryCommand command2 = new QueryCommand("SELECT\r\n    KCU.TABLE_NAME as TableName,\r\n    KCU.TABLE_SCHEMA as Owner,\r\n    KCU.COLUMN_NAME as ColumnName, \r\n    TC.CONSTRAINT_TYPE as ConstraintType \r\nFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\nJOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC\r\nON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME", this.Name);
                DataTable table3 = new DataTable(this.Name);
                table3.Load(this.GetReader(command2));
                dsIndex.Tables.Add(table3);
            }
            DataRow[] rowArray2 = dsIndex.Tables[this.Name].Select("TableName ='" + tableName + "'");
            for (int j = 0; j < rowArray2.Length; j++)
            {
                string columnName = rowArray2[j]["ColumnName"].ToString();
                string stringA = rowArray2[j]["constraintType"].ToString();
                TableSchema.TableColumn column = columns.GetColumn(columnName);
                if (Utility.IsMatch(stringA, "PRIMARY KEY"))
                {
                    column.IsPrimaryKey = true;
                }
                else if (Utility.IsMatch(stringA, "FOREIGN KEY"))
                {
                    column.IsForeignKey = true;
                }
                column.ColumnName = column.ColumnName;
                this.SetExtendedColumnProperties(tblSchema, column);
            }
            if (dsPK.Tables[this.Name] == null)
            {
                QueryCommand command3 = new QueryCommand("SELECT \r\n    TC.TABLE_NAME AS PK_TABLE,\r\n    KCU.TABLE_NAME AS FK_TABLE,\r\n    KCU.COLUMN_NAME AS FK_COLUMN\r\nFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU\r\nJOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_NAME=RC.CONSTRAINT_NAME\r\nJOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON RC.UNIQUE_CONSTRAINT_NAME=TC.CONSTRAINT_NAME", this.Name);
                DataTable table4 = new DataTable(this.Name);
                table4.Load(this.GetReader(command3));
                dsPK.Tables.Add(table4);
            }
            DataRow[] rowArray3 = dsPK.Tables[this.Name].Select("PK_Table ='" + tableName + "'");
            for (int k = 0; k < rowArray3.Length; k++)
            {
                string str5 = rowArray3[k]["FK_Column"].ToString();
                string str6 = rowArray3[k]["FK_Table"].ToString();
                TableSchema.PrimaryKeyTable table5 = new TableSchema.PrimaryKeyTable(this);
                table5.ColumnName = str5;
                table5.TableName = str6;
                this.SetExtendedTableProperties(table5);
                tblSchema.PrimaryKeyTables.Add(table5);
            }
            if (dsFK.Tables[this.Name] == null)
            {
                QueryCommand command4 = new QueryCommand("SELECT\r\n    FK_Table  = FK.TABLE_NAME,\r\n    FK_Column = CU.COLUMN_NAME,\r\n    PK_Table  = PK.TABLE_NAME,\r\n    PK_Column = PT.COLUMN_NAME, \r\n    Constraint_Name = C.CONSTRAINT_NAME,\r\n    Owner = FK.TABLE_SCHEMA\r\nFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C\r\nINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME\r\nINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME\r\nINNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME\r\nINNER JOIN\r\n    (\t\r\n        SELECT i1.TABLE_NAME, i2.COLUMN_NAME\r\n        FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1\r\n        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME\r\n        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'\r\n    ) \r\nPT ON PT.TABLE_NAME = PK.TABLE_NAME", this.Name);
                DataTable table6 = new DataTable(this.Name);
                table6.Load(this.GetReader(command4));
                dsFK.Tables.Add(table6);
            }
            DataRow[] rowArray4 = dsFK.Tables[this.Name].Select("FK_Table ='" + tableName + "'");
            ArrayList list = new ArrayList();
            for (int m = 0; m < rowArray4.Length; m++)
            {
                string str7 = rowArray4[m]["Constraint_Name"].ToString();
                if (!list.Contains(str7))
                {
                    list.Add(str7);
                    string str8 = rowArray4[m]["FK_Column"].ToString();
                    string str9 = rowArray4[m]["PK_Table"].ToString();
                    columns.GetColumn(str8).ForeignKeyTableName = str9;
                    TableSchema.ForeignKeyTable table7 = new TableSchema.ForeignKeyTable(this);
                    table7.ColumnName = str8;
                    table7.TableName = str9;
                    this.SetExtendedTableProperties(table7);
                    tblSchema.ForeignKeys.Add(table7);
                }
            }
            if (dsManyToManyCheck.Tables[this.Name] == null)
            {
                QueryCommand command5 = new QueryCommand("SELECT \r\n    FK_Table = FK.TABLE_NAME, \r\n    FK_Column = CU.COLUMN_NAME, \r\n    PK_Table  = PK.TABLE_NAME, \r\n    PK_Column = PT.COLUMN_NAME, \r\n    Constraint_Name = C.CONSTRAINT_NAME\r\nFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C\r\nINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME\r\nINNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME\r\nINNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME\r\nINNER JOIN    \r\n(    \r\n    SELECT i1.TABLE_NAME, i2.COLUMN_NAME\r\n    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1\r\n    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME\r\n    WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'\r\n)\r\nPT ON PT.TABLE_NAME = PK.TABLE_NAME\r\n\r\nWHERE FK.TABLE_NAME IN\r\n    (\r\n        SELECT tc.table_Name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc\r\n        JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON tc.Constraint_name = kcu.Constraint_Name\r\n        JOIN \r\n        (\r\n            SELECT tc.Table_Name, kcu.Column_Name AS Column_Name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc\r\n            JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu ON tc.Constraint_name = kcu.Constraint_Name\r\n            WHERE Constraint_Type = 'FOREIGN KEY'\r\n        ) \r\n        AS t ON t.Table_Name = tc.table_Name AND t.Column_Name = kcu.Column_Name\r\n        WHERE Constraint_Type = 'PRIMARY KEY'\r\n        GROUP BY tc.Constraint_Name, tc.Table_Name HAVING COUNT(*) > 1   \r\n    )", this.Name);
                DataTable table8 = new DataTable(this.Name);
                table8.Load(this.GetReader(command5));
                dsManyToManyCheck.Tables.Add(table8);
            }
            DataRow[] rowArray5 = dsManyToManyCheck.Tables[this.Name].Select("PK_Table = '" + tableName + "'");
            if (rowArray5.Length > 0)
            {
                for (int n = 0; n < rowArray5.Length; n++)
                {
                    string str10 = rowArray5[n]["FK_Table"].ToString();
                    string str11 = rowArray5[n]["FK_Column"].ToString();
                    if (dsManyToManyMap.Tables[this.Name] == null)
                    {
                        QueryCommand command6 = new QueryCommand("SELECT \r\n    FK_Table  = FK.TABLE_NAME,\r\n    FK_Column = CU.COLUMN_NAME,\r\n    PK_Table  = PK.TABLE_NAME,\r\n    PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME\r\nFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C\r\nINNER JOIN \tINFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME\r\nINNER JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME\r\nINNER JOIN  INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME\r\nINNER JOIN\t\r\n    (\r\n        SELECT i1.TABLE_NAME, i2.COLUMN_NAME\r\n        FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1\r\n        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME\r\n        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'\r\n    ) \r\nPT ON PT.TABLE_NAME = PK.TABLE_NAME", this.Name);
                        DataTable table9 = new DataTable(this.Name);
                        table9.Load(this.GetReader(command6));
                        dsManyToManyMap.Tables.Add(table9);
                    }
                    DataRow[] rowArray6 = dsManyToManyMap.Tables[this.Name].Select("FK_Table = '" + str10 + "' AND PK_Table <> '" + tableName + "'");
                    for (int num7 = 0; num7 < rowArray6.Length; num7++)
                    {
                        TableSchema.ManyToManyRelationship relationship = new TableSchema.ManyToManyRelationship(str10, tblSchema.Provider);
                        relationship.ForeignTableName = rowArray6[num7]["PK_Table"].ToString();
                        relationship.ForeignPrimaryKey = rowArray6[num7]["PK_Column"].ToString();
                        relationship.MapTableLocalTableKeyColumn = str11;
                        relationship.MapTableForeignTableKeyColumn = rowArray6[num7]["FK_Column"].ToString();
                        tblSchema.ManyToManys.Add(relationship);
                    }
                }
            }
            tblSchema.Columns = columns;
            return tblSchema;
        }

        public override string[] GetViewNameList()
        {
            if ((base.ViewNames == null) || !base.CurrentConnectionStringIsDefault)
            {
                QueryCommand cmd = new QueryCommand("/* GetViewNameList() */ SELECT TABLE_CATALOG AS [Database], TABLE_SCHEMA AS Owner, TABLE_NAME AS Name, TABLE_TYPE\r\n                                        FROM INFORMATION_SCHEMA.TABLES\r\n                                        WHERE (TABLE_TYPE = 'VIEW') AND (TABLE_NAME <> N'sysdiagrams')", this.Name);
                StringBuilder builder = new StringBuilder();
                using (IDataReader reader = this.GetReader(cmd))
                {
                    while (reader.Read())
                    {
                        string str = reader["Name"].ToString();
                        if (string.IsNullOrEmpty(base.ViewStartsWith) || str.StartsWith(base.ViewStartsWith))
                        {
                            builder.Append(reader["Name"]);
                            builder.Append("|");
                        }
                    }
                    reader.Close();
                }
                string[] array = builder.ToString().Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
                Array.Sort<string>(array);
                if (!base.CurrentConnectionStringIsDefault)
                {
                    return array;
                }
                base.ViewNames = array;
            }
            return base.ViewNames;
        }

        private void LoadExtendedPropertyDataSet()
        {
            if (dsExtendedProperties.Tables[this.Name] == null)
            {
                QueryCommand cmd = new QueryCommand("SELECT \r\n    t.name AS [TABLE_NAME], \r\n    c.name AS [COLUMN_NAME], \r\n    ep.name AS [EXTENDED_NAME],\r\n    value AS [EXTENDED_VALUE]\r\nFROM sys.extended_properties AS ep\r\nINNER JOIN sys.tables AS t ON ep.major_id = t.object_id \r\nLEFT JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id\r\nWHERE class = 1 OR class = 3", this.Name);
                DataTable table = new DataTable(this.Name);
                table.Load(this.GetReader(cmd));
                dsExtendedProperties.Tables.Add(table);
            }
        }

        public override string ScriptData(string tableName)
        {
            return this.ScriptData(tableName, DataService.Provider.Name);
        }

        public override string ScriptData(string tableName, string providerName)
        {
            StringBuilder builder = new StringBuilder();
            StringBuilder builder2 = new StringBuilder();
            StringBuilder builder3 = new StringBuilder();
            StringBuilder builder4 = new StringBuilder();
            StringBuilder builder5 = new StringBuilder();
            builder5.AppendLine("ALTER TABLE [" + tableName + "] NOCHECK CONSTRAINT ALL");
            builder5.AppendLine("GO");
            StringBuilder builder6 = new StringBuilder();
            builder6.AppendLine("ALTER TABLE [" + tableName + "] CHECK CONSTRAINT ALL");
            builder6.AppendLine("GO");
            builder2.Append("INSERT INTO [" + tableName + "] ");
            TableSchema.Table tbl = Query.BuildTableSchema(tableName, providerName);
            string columnName = tbl.Columns[tbl.Columns.Count - 1].ColumnName;
            foreach (TableSchema.TableColumn column in tbl.Columns)
            {
                builder.Append("[");
                builder.Append(column.ColumnName);
                builder.Append("]");
                if (!Utility.IsMatch(column.ColumnName, columnName))
                {
                    builder.Append(", ");
                }
            }
            builder2.Append("(");
            builder2.Append(builder);
            builder2.AppendLine(")");
            IDataReader reader = new Query(tbl).ExecuteReader();
            while (reader.Read())
            {
                StringBuilder builder7 = new StringBuilder();
                builder7.Append(builder2);
                builder7.Append("VALUES(");
                foreach (TableSchema.TableColumn column2 in tbl.Columns)
                {
                    object obj2 = reader[column2.ColumnName];
                    if ((obj2 != null) && (obj2 != DBNull.Value))
                    {
                        if (column2.DataType == DbType.Boolean)
                        {
                            bool flag = Convert.ToBoolean(obj2);
                            builder7.Append(flag ? "1" : " 0");
                        }
                        else if ((column2.DataType == DbType.Byte) || (column2.DataType == DbType.Binary))
                        {
                            builder7.Append("0x");
                            builder7.Append(Utility.ByteArrayToString((byte[]) obj2).ToUpper());
                        }
                        else if (column2.IsNumeric)
                        {
                            builder7.Append(obj2);
                        }
                        else
                        {
                            builder7.Append("'");
                            builder7.Append(obj2.ToString().Replace("'", "''"));
                            builder7.Append("'");
                        }
                    }
                    else
                    {
                        builder7.Append("NULL");
                    }
                    if (!Utility.IsMatch(column2.ColumnName, columnName))
                    {
                        builder7.Append(", ");
                    }
                }
                builder7.AppendLine(")");
                builder3.Append(builder7);
            }
            reader.Close();
            builder4.AppendLine(builder5.ToString());
            if ((tbl.PrimaryKey != null) && tbl.PrimaryKey.AutoIncrement)
            {
                builder4.AppendLine("SET IDENTITY_INSERT [" + tableName + "] ON ");
            }
            builder4.AppendLine("PRINT 'Begin inserting data in " + tableName + "'");
            builder4.Append(builder3);
            if ((tbl.PrimaryKey != null) && tbl.PrimaryKey.AutoIncrement)
            {
                builder4.AppendLine("SET IDENTITY_INSERT [" + tableName + "] OFF ");
            }
            builder4.AppendLine(builder6.ToString());
            return builder4.ToString();
        }

        public override string ScriptSchema()
        {
            string str = "";
            return (str + Environment.NewLine + Environment.NewLine);
        }

        private void SetExtendedColumnProperties(TableSchema.AbstractTableSchema tblSchema, TableSchema.TableColumn col)
        {
            if (base.UseExtendedProperties)
            {
                DataRow[] rowArray = null;
                if (Utility.IsSql2005(this))
                {
                    this.LoadExtendedPropertyDataSet();
                    rowArray = dsExtendedProperties.Tables[this.Name].Select("TABLE_NAME ='" + tblSchema.TableName + "' AND COLUMN_NAME = '" + col.ColumnName + "'");
                }
                else if (Utility.IsSql2000(this))
                {
                    QueryCommand cmd = new QueryCommand(string.Format("SELECT \r\n    objname AS COLUMN_NAME,\r\n    [name] AS EXTENDED_NAME,\r\n    [value] AS EXTENDED_VALUE\r\nFROM ::fn_listextendedproperty (NULL, 'user', '{0}', 'TABLE', '{1}', 'COLUMN', DEFAULT);", tblSchema.SchemaName, tblSchema.TableName), this.Name);
                    DataTable table = new DataTable(this.Name);
                    table.Load(this.GetReader(cmd));
                    rowArray = table.Select("COLUMN_NAME ='" + col.ColumnName + "'");
                }
                if (rowArray != null)
                {
                    for (int i = 0; i < rowArray.Length; i++)
                    {
                        string key = rowArray[i]["EXTENDED_NAME"].ToString();
                        if (!col.ExtendedProperties.Contains(key))
                        {
                            col.ExtendedProperties.Add(new TableSchema.ExtendedProperty(key, rowArray[i]["EXTENDED_VALUE"].ToString()));
                        }
                        col.ApplyExtendedProperties();
                    }
                }
            }
        }

        private void SetExtendedTableProperties(TableSchema.AbstractTableSchema tblSchema)
        {
            if (base.UseExtendedProperties)
            {
                DataRow[] rowArray = null;
                if (Utility.IsSql2005(this))
                {
                    this.LoadExtendedPropertyDataSet();
                    rowArray = dsExtendedProperties.Tables[this.Name].Select("TABLE_NAME ='" + tblSchema.TableName + "' AND COLUMN_NAME IS NULL");
                }
                else if (Utility.IsSql2000(this))
                {
                    QueryCommand cmd = new QueryCommand(string.Format("SELECT \r\n    objname AS TABLE_NAME,\r\n    [name] AS EXTENDED_NAME,\r\n    [value] AS EXTENDED_VALUE\r\nFROM ::fn_listextendedproperty (NULL, 'user', '{0}', 'TABLE', default, NULL, NULL);", tblSchema.SchemaName), this.Name);
                    DataTable table = new DataTable(this.Name);
                    table.Load(this.GetReader(cmd));
                    rowArray = table.Select("TABLE_NAME ='" + tblSchema.TableName + "'");
                }
                if (rowArray != null)
                {
                    for (int i = 0; i < rowArray.Length; i++)
                    {
                        tblSchema.ExtendedProperties.Add(new TableSchema.ExtendedProperty(rowArray[i]["EXTENDED_NAME"].ToString(), rowArray[i]["EXTENDED_VALUE"].ToString()));
                    }
                    tblSchema.ApplyExtendedProperties();
                }
            }
        }

        public override void SetParameter(IDataReader rdr, StoredProcedure.Parameter par)
        {
            par.DBType = this.GetDbType(rdr["DataType"].ToString());
            if (rdr["mode"].ToString() == "INOUT")
            {
                par.Mode = ParameterDirection.InputOutput;
            }
            par.Name = rdr["Name"].ToString();
        }
    }
}

